package com.davesandoval.tpdroid;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.sax.StartElementListener;

public class TPdbAdapter {
	private SQLiteDatabase db;
	private DataBaseHelper dbHelper;
	private Context context;

	// Database fields
	private static final String DATABASE_TABLE = "openSO";
	public static final String KEY_ROW_ID = "_id";
	public static final String KEY_SO_NUM = "soNumber";
	public static final String KEY_ACCOUNT_NAME = "accountName";
	public static final String KEY_ACCOUNT_NUMBER = "accountNumber";
	public static final String KEY_DESCRIPTION = "description";
	public static final String KEY_BRIEF_DESCRIPTION = "briefDescription";
	public static final String KEY_PRIORITY = "priority";
	public static final String KEY_REQ_DATE = "reqDate";
	public static final String KEY_STATUS = "status";
	public static final String KEY_WORK_PERFORMED = "workPerformed";
	public static final String KEY_LOG_DETAIL = "logDetail";
	public static final String KEY_SO_REASON = "soReason";
	public static final String KEY_START_TIME = "startTime";
	public static final String KEY_END_TIME = "endTime";
	public static final String KEY_START_DATE = "startDate";
	public static final String KEY_END_DATE = "endDate";
	public static final String KEY_NEW_STATUS = "newStatus";
	public static final String KEY_COPY_LC = "copylc";
	public static final String KEY_BLN_TASK = "blntask";
	public static final String KEY_STRRATEBASIS = "strRateBasis";
	public static final String KEY_SO_BLN_TASK = "soBlntask";
	public static final String KEY_CONTACT_NAME = "contactName";
	public static final String KEY_CONTACT_PHONE = "contactPhone";
	public static final String KEY_CHARGEABLE = "chargeable";
	public static final String KEY_BILLABLE = "billable";
	public static final String KEY_ADDRESS = "address";

	// Default constructor

	public TPdbAdapter(Context context) {
		this.context = context;
	}

	public TPdbAdapter open() throws SQLException {
		dbHelper = new DataBaseHelper(context);
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		dbHelper.close();
		db.close();
	}

	public long addSO(String soNumber, String accountName, String description,
			String priority, String reqDate, String status,
			String briefDescription, String workPerformed, String accountNumber,
			String soBlntask, String strRateBasis) {
		ContentValues initialValues = createContentValues(soNumber,
				accountName, description, priority, reqDate, status,
				briefDescription, workPerformed, accountNumber, soBlntask, strRateBasis);

		return db.insert(DATABASE_TABLE, null, initialValues);
	}

	public long addSOLog(String soNumber, String logDetail, String soReason, String startTime,
			String endTime, String startDate, String endDate, String newStatus,
			String chargeable, String billable, String copylc, String blntask) {
		ContentValues initialValues = createContentValuesLogs(soNumber,
				logDetail, soReason, startTime, endTime, startDate, endDate, newStatus,
				chargeable, billable, copylc, blntask);

		return db.insert("soLogs", null, initialValues);
	}

	public long addAccount(String accountName, String contactName,
			String contactPhone, String address, String accountNumber) {
		ContentValues initialValues = createContentValuesAccounts(accountName,
				contactName, contactPhone, address, accountNumber);

		return db.insert("accounts", null, initialValues);
	}

	public long updateSO(String soNumber, String accountName,
			String description, String priority, String reqDate, String status,
			String briefDescription, String workPerformed, String accountNumber,
			String soBlntask, String strRateBasis) {
		ContentValues initialValues = createContentValues(soNumber,
				accountName, description, priority, reqDate, status,
				briefDescription, workPerformed, accountNumber, soBlntask,
				strRateBasis);

		return db.update(DATABASE_TABLE, initialValues, KEY_SO_NUM + "="
				+ soNumber, null);
	}

	public long updateSOLog(String soNumber, String logDetail, String soReason, String startTime,
			String endTime, String startDate, String endDate, String newStatus,
			String chargeable, String billable, String copylc, String blntask) {
		ContentValues initialValues = createContentValuesLogs(soNumber,
				logDetail, soReason, startTime, endTime, startDate, endDate, newStatus,
				chargeable, billable, copylc, blntask);

		return db.update("soLogs", initialValues, KEY_SO_NUM + "="
				+soNumber, null);
	}

	private ContentValues createContentValues(String soNumber,
			String accountName, String description, String priority,
			String reqDate, String status, String briefDescription,
			String workPerformed, String accountNumber, String soBlntask,
			String strRateBasis) {
				ContentValues values = new ContentValues();
				values.put(KEY_SO_NUM, soNumber);
				values.put(KEY_ACCOUNT_NAME, accountName);
				values.put(KEY_PRIORITY, priority);
				values.put(KEY_STATUS, status);
				values.put(KEY_REQ_DATE, reqDate);
				values.put(KEY_DESCRIPTION, description);
				values.put(KEY_BRIEF_DESCRIPTION, briefDescription);
				values.put(KEY_WORK_PERFORMED, workPerformed);
				values.put(KEY_ACCOUNT_NUMBER, accountNumber);
				values.put(KEY_SO_BLN_TASK, soBlntask);
				values.put(KEY_STRRATEBASIS, strRateBasis);
				return values;
	}

	private ContentValues createContentValuesLogs(String soNumber,
			String logDetail, String soReason, String startTime, String endTime,
			String startDate, String endDate, String newStatus,
			String chargeable, String billable, String copylc,
			String blntask) {
				ContentValues values = new ContentValues();
				values.put(KEY_SO_NUM, soNumber);
				values.put(KEY_LOG_DETAIL, logDetail);
				values.put(KEY_SO_REASON, soReason);
				values.put(KEY_START_TIME, startTime);
				values.put(KEY_END_TIME, endTime);
				values.put(KEY_START_DATE, startDate);
				values.put(KEY_END_DATE, endDate);
				values.put(KEY_NEW_STATUS, newStatus);
				values.put(KEY_CHARGEABLE, chargeable);
				values.put(KEY_BILLABLE, billable);
				values.put(KEY_COPY_LC, copylc);
				values.put(KEY_BLN_TASK, blntask);
				return values;
	}

	private ContentValues createContentValuesAccounts(String accountName,
			String contactName, String contactPhone, String address, String accountNumber) {
				ContentValues values = new ContentValues();
				values.put(KEY_ACCOUNT_NAME, accountName);
				values.put(KEY_CONTACT_NAME, contactName);
				values.put(KEY_CONTACT_PHONE, contactPhone);
				values.put(KEY_ADDRESS, address);
				values.put(KEY_ACCOUNT_NUMBER, accountNumber);
				return values;
	}

	public Cursor fetchSOViaSONum(String soNum) throws SQLException {
		Cursor tempCursor = db.query(DATABASE_TABLE, null, KEY_SO_NUM + "="
				+ soNum, null, null, null, null);
		if (tempCursor != null) {
			tempCursor.moveToFirst();
		}
		return tempCursor;
	}

	public Cursor fetchSOLogViaSONum(String soNum) throws SQLException {
		Cursor tempCursor = db.query("soLogs", null, KEY_SO_NUM + "="
				+ soNum, null, null, null, null);
		if (tempCursor != null) {
			tempCursor.moveToFirst();
		}
		return tempCursor;
	}

	public Cursor fetchSO(long rowid) throws SQLException {
		Cursor tempCursor = db.query(DATABASE_TABLE, null, KEY_ROW_ID + "="
				+ rowid, null, null, null, null);
		if (tempCursor.getCount() > 0) {
			tempCursor.moveToFirst();
		}
		return tempCursor;
	}

	public Cursor fetchAllSO() throws SQLException {
		Cursor tempCursor = db.query(DATABASE_TABLE, null, null, null, null,
				null, null);
		if (tempCursor.getCount() > 0) {
			tempCursor.moveToFirst();
		}
		return tempCursor;
	}

	public boolean soExists(String soNum) {
		Cursor tempCursor = db.query(DATABASE_TABLE, null, KEY_SO_NUM + "="
				+ soNum, null, null, null, null);
		tempCursor.moveToFirst();
		if (tempCursor.getCount() > 0) {
			tempCursor.deactivate();
			return true;
		}
		tempCursor.deactivate();
		return false;
	}

	public boolean soLogExists(String soNum) {
		Cursor tempCursor = db.query("soLogs", null, KEY_SO_NUM + "="
				+ soNum, null, null, null, null);
		tempCursor.moveToFirst();
		if (tempCursor.getCount() > 0) {
			tempCursor.deactivate();
			return true;
		}
		tempCursor.deactivate();
		return false;
	}

	public Cursor fetchAllLogs() throws SQLException {
		Cursor tempCursor = db.query("soLogs", null, null, null, null,
				null, null);
		if (tempCursor.getCount() > 0) {
			tempCursor.moveToFirst();
		}
		return tempCursor;
	}

	public Cursor fetchAccountViaNumber(String accountNumber) throws SQLException {
		Cursor tempCursor = db.query("accounts", null, KEY_ACCOUNT_NUMBER + "="
				+ accountNumber, null, null, null, null);
		if (tempCursor != null) {
			tempCursor.moveToFirst();
		}
		return tempCursor;
	}

	public boolean accountExists(String accountNumber) {
		Cursor tempCursor = db.query("accounts", null, KEY_ACCOUNT_NUMBER + "="
				+ accountNumber, null, null, null, null);
		tempCursor.moveToFirst();
		if (tempCursor.getCount() > 0) {
			tempCursor.deactivate();
			return true;
		}
		tempCursor.deactivate();
		return false;
	}

	public boolean isOpen() {
		return db.isOpen();
	}
	
	public void cleanSO() {
		db.execSQL("delete from openSO;");
	}
	
	public void dropOfflineLogs(String soNum){
		db.execSQL("delete from soLogs where soNumber =" + soNum + ";");
	}

	public void dropAllOfflineLogs(){
		db.execSQL("delete from soLogs;");
	}
}